#! /usr/bin/python
# -*- coding: cp1250 -*-

import glob, sys, os, re, string
import pepe.p_txt.bigtxt as bt

def createTableString(inFile, tableName, colDelimiter="^", hasHeader=0):
    ''' Purpose:    Define columns lenghts (TODO: and type). Create pg table sql.
        Todo:       Guess columns type.
    '''
    res = ''
    out_h = {}
    header_l = []
    flcnt_n = 0
    #print inFile
    for line_s in bt.lines(inFile):
        flcnt_n += 1
        
        #2011-06-14_1501 vip remove EOLs
        line_s = re.sub('\x0D\x0A', '', line_s)
        line_s = re.sub('\x0D', '', line_s)
        line_s = re.sub('\x0A', '', line_s)
        
        #columns_l = string.split(line_s[:-1], colDelimiter)
        columns_l = string.split(line_s, colDelimiter)
        for index, value in enumerate(columns_l):
            index = 'c'+string.zfill(index+1,3)

            '''
            if  hasHeader <> 0 and flcnt_n == 1:
                #use cXXX form form column names
                #header_l = columns_l
                # do nothing except skipping columns span calculation for header row
            else:
            '''
            if out_h.get(index):
                if  out_h[index] < len(str(value)):
                    out_h[index] = len(str(value))
            else:
                out_h[index] = len(str(value))

	tbname = tableName
    res += "DROP TABLE IF EXISTS "+tbname+";\nCREATE  TABLE "+tbname+"("
    for i in sorted(out_h.keys()):
        if hasHeader <> 0:
            res +=  "\n"+" "*8 + ', ' + str(i) +' varchar('+str(out_h[i]+1) + ')'
            #res +=  "\n"+" "*8 + ', ' + str(i) +'_'+ re.sub('\s{1,}', '', header_l[int(i[1:])-1]) +' VARCHAR('+str(out_h[i]+1) + ')'
        else:
            res +=  "\n"+" "*8 + ', ' + str(i) +' varchar('+str(out_h[i]+1) + ')'
    res = re.sub("\(\n        ,", "(\n         ", res)
    res += "\n        ) WITH OIDS \n"

    if res <> '': return res
    else: return 0

def main(inFile, colDelimiter="^", hasHeader=0, ofile='csv2pg.out'):
    """
    Purpose:    Define columns lenghts. Create pg table sql.
    Syntax:     csv2pg <csv file> "<column delimiter>" <has header 1/0>
    Example:    csv2pg test.csv "^" 1

    Todo: Guess columns type.
    """
    # Print output to file
    out_fl = open(ofile,'w')
    out_fl.write(createTableString(inFile, colDelimiter, hasHeader))
    out_fl.close()
    
if __name__ == "__main__":
    """ """
    if len(sys.argv) == 3:
        main(sys.argv[1], sys.argv[2])
    elif len(sys.argv) == 4:
        main(sys.argv[1], sys.argv[2], sys.argv[3])
    else:
        print main.__doc__
